﻿using System;
using System.Collections.Generic;
using T.FAS.Runtime.Base.Entity;

namespace T.FAS.Runtime.DataModel.Plugin
{
    public class MetaManagerConst 
    {
        public const string db_name = "db_name";
        public const string table_name = "table_name";
        public const string column_name = "column_name";
        public const string data_type = "data_type";
        public const string length = "length";
        public const string @default = "default";
        public const string precision = "precision";
        public const string nullable = "nullable";
        public const string comment = "comment";
        public const string pk_name = "pk_name";
        public const string pk_column_name = "pk_column_name";
        public const string column_modify_details = "column_modify_details";

        /// <summary>
        /// MSSQL数据库SQL模板类
        /// </summary>
        public class MSSQL
        {
            #region Const
            /// <summary>
            /// 创建表模板
            /// </summary>
            public const string CreateTable =
@"

/*请确定Sql的正确性*/
if not exists( select 1 from sysobjects where id = object_id('[{table_name}]') and type = 'u')
CREATE TABLE [{table_name}] (
{column_def}
{pk_constraint_def}
);


";

            /// <summary>
            /// 创建表时列定义模板
            /// </summary>
            public const string CreateTableColumnDef =
@"[{column_name}]    {data_type}    {nullable} {default} ,
";

            /// <summary>
            /// 创建表时主键定义模板
            /// </summary>
            public const string CreateTablePKDef =
@"CONSTRAINT  [{pk_name}] PRIMARY KEY ([{pk_column_name}])
";

            /// <summary>
            /// 索引定义模板
            /// </summary>
            public const string IndexDef =
@"
IF NOT EXISTS(select * from sys.indexes where object_id = object_id(N'[{table_name}]') and name = '{index_name}')
CREATE  {clustered} INDEX [{index_name}] on [{table_name}]({index_column_colle})  Include([{include_column_colle}])

";
            /// <summary>
            /// 修改列模板
            /// </summary>
            public const string ColumnModify =
@"if exists(select* from syscolumns where id = OBJECT_ID('[{table_name}]')   and name = '{column_name}')
begin
    ALTER TABLE [{table_name}] ALTER COLUMN [{column_name}] {data_type} {nullable} 
end

";
            /// <summary>
            /// 删除主键约束 
            /// https://docs.microsoft.com/en-us/sql/relational-databases/tables/modify-primary-keys?view=sql-server-ver15
            /// https://docs.microsoft.com/en-us/sql/relational-databases/tables/delete-primary-keys?view=sql-server-ver15
            /// </summary>
            public const string DropPKConstraint =
@"DECLARE @PKNAME SYSNAME
SELECT TOP 1 @PKNAME = NAME FROM sysobjects WITH(NOLOCK) where parent_obj = object_id(N'[{table_name}]') and xtype = 'PK'
DECLARE @ALTERSQL NVARCHAR(MAX)
SET @ALTERSQL = N'ALTER TABLE [{table_name}] DROP CONSTRAINT ' + @PKNAME
IF EXISTS(select 1 from sysobjects where parent_obj = object_id(N'[{table_name}]') and xtype = 'PK')
EXEC SP_EXECUTESQL @ALTERSQL

";

            /// <summary>
            /// 新增主键约束
            /// https://docs.microsoft.com/en-us/sql/relational-databases/tables/create-primary-keys?view=sql-server-ver15
            /// </summary>
            public const string PKAdd =
@"IF NOT EXISTS(select * from sysobjects where parent_obj = object_id(N'[{table_name}]') and xtype = 'PK')
BEGIN
    ALTER TABLE [{table_name}] ADD CONSTRAINT  PK_{table_name} PRIMARY KEY({pk_column_name})
END

";

            /// <summary>
            /// 新增列模板
            /// </summary>
            public const string ColumnAdd =
@"IF NOT EXISTS( SELECT 1 FROM syscolumns WHERE ID = OBJECT_ID('[{table_name}]') AND name='{column_name}' )
begin
    ALTER TABLE [{table_name}] ADD [{column_name}] {data_type} {nullable} {default}
end

";
            /// <summary>
            /// 删除列模板
            /// </summary>
            public const string ColumnDrop =
@"if exists(select* from syscolumns where id = OBJECT_ID('[{table_name}]')   and name = '{column_name}')
begin
    ALTER TABLE [{table_name}] DROP COLUMN [{column_name}]
end

";

            /// <summary>
            /// 删除某列的默认值约束
            /// </summary>
            public const string DefaultDrop =
@"IF EXISTS( select b.name from sysobjects b join syscolumns a on b.id = a.cdefault  WHERE a.id = OBJECT_ID('[{table_name}]') AND a.name='{column_name}' )
begin
declare @dfname{column_name} varchar(100)
select @dfname{column_name} =b.name from sysobjects b join syscolumns a on b.id = a.cdefault where a.id = object_id('{table_name}') and a.name='{column_name}' 
if @dfname{column_name} is not null
begin
exec('alter table [{table_name}] drop constraint '+ @dfname{column_name})
end
end

";


            /// <summary>
            /// 添加某列的默认值约束
            /// </summary>
            public const string DefaultAdd =
@"IF not EXISTS( select b.name from sysobjects b join syscolumns a on b.id = a.cdefault  WHERE a.id = OBJECT_ID('[{table_name}]') AND a.name='{column_name}' )
begin
declare @adddfname{column_name} varchar(100)
select @adddfname{column_name} =b.name from sysobjects b join syscolumns a on b.id = a.cdefault where a.id = object_id('{table_name}') and a.name='{column_name}' 
if @adddfname{column_name} is null
begin
ALTER TABLE {table_name}
ADD CONSTRAINT df_{table_name}_{column_name}
{default} FOR {column_name};
end
end

";

            public const string INSERT =
@"
INSERT INTO {table_name} ({column_names}) values ({column_values});";

            public const string DELETE =
                @"
DELETE FROM {table_name} {condition};";

            #endregion

            /// <summary>
            /// 后期改成可配置
            /// </summary>
            public readonly static HashSet<string> FunctionList = new HashSet<string>
            {
                "getdate()", "sysdatetime()", "newid()"
            };

            /// <summary>
            /// 数值类型字典
            /// </summary>
            public readonly static HashSet<string> ValueType = new HashSet<string>
            {
                DatabaseColumnType.Bit.ToString().ToLower(),
                DatabaseColumnType.TinyInt.ToString().ToLower(),
                DatabaseColumnType.SmallInt.ToString().ToLower(),
                DatabaseColumnType.Int.ToString().ToLower(),
                DatabaseColumnType.BigInt.ToString().ToLower(),
                DatabaseColumnType.Decimal.ToString().ToLower(),
                DatabaseColumnType.Binary.ToString().ToLower(),
            };
        }

        /// <summary>
        /// MySQL数据库SQL模板类
        /// https://dev.mysql.com/doc/refman/5.7/en/create-table.html
        /// </summary>
        public class MySQL
        {
            #region Const
            /// <summary>
            /// 创建表模板
            /// </summary>
            public const string CreateTable =
@"/*请确定Sql的正确性*/
CREATE TABLE IF NOT EXISTS `{table_name}`(
{column_def}
{pk_constraint_def}
);";
            /// <summary>
            /// 创建表时列定义模板
            /// </summary>
            public const string CreateTableColumnDef =
@"`{column_name}`    {data_type}    {nullable} {default} {comment} ,
";

            /// <summary>
            /// 创建表时主键定义模板
            /// </summary>
            public const string CreateTablePKDef =
@"PRIMARY KEY (`{pk_column_name}`)
";

            /// <summary>
            /// 索引定义模板
            /// </summary>
            public const string IndexDef =
@"CALL Pro_MetaData_IndexWork ('{table_name}','{index_type}','{index_name}','(`{index_column_colle}`)', 1);";

            /// <summary>
            /// 修改列模板
            /// </summary>
            public const string ColumnModify =
@"alter table `{table_name}` modify column `{column_name}` {data_type} {nullable} {default} {comment} ;
";
            /// <summary>
            /// 新增列模板
            /// </summary>
            public const string ColumnAdd =
//"alter table `{table_name}` add `{column_name}` {data_type} {nullable} {default}  {comment}  ;\r\n";
"CALL Pro_MetaData_ColumnWork ('{table_name}','{column_name}','`{column_name}`    {data_type}    {nullable}    {default}  {comment}', 1);\r\n";

            /// <summary>
            /// 删除列模板
            /// </summary>
            public const string ColumnDrop =
@"alter table `{table_name}` drop column `{column_name}`;
";


            public const string INSERT =
@"insert into {table_name} ({column_names}) values ({column_values});";

            public const string DELETE =
                @"delete from {table_name} {condition};";

            /// <summary>
            /// 添加某列的默认值约束
            /// </summary>
            public const string DefaultChange =
@"ALTER TABLE {table_name} alter column {column_names} set {default};";
            #endregion

            /// <summary>
            /// 后期改成可配置
            /// </summary>


            public readonly static HashSet<string> FunctionList = new HashSet<string>
            {
                "now()", "sysdate()", "curdate()", "curtime()"
            };

            /// <summary>
            /// 数值类型字典
            /// </summary>
            public readonly static HashSet<string> ValueType = new HashSet<string>
            {
                DatabaseColumnType.Bit.ToString().ToLower(),
                DatabaseColumnType.TinyInt.ToString().ToLower(),
                DatabaseColumnType.SmallInt.ToString().ToLower(),
                DatabaseColumnType.Int.ToString().ToLower(),
                DatabaseColumnType.BigInt.ToString().ToLower(),
                DatabaseColumnType.Decimal.ToString().ToLower(),
                DatabaseColumnType.Binary.ToString().ToLower(),
            };
        }

        public class Postgres
        {
            #region Const
            /// <summary>
            /// 创建表模板
            /// </summary>
            public const string CreateTable =
@"/*请确定Sql的正确性*/
CREATE TABLE IF NOT EXISTS ""{table_name}""(
{column_def}
{pk_constraint_def}
);";
            /// <summary>
            /// 创建表时列定义模板
            /// </summary>
            public const string CreateTableColumnDef =
@"""{column_name}"" {data_type} {nullable} {default},
";

            /// <summary>
            /// 创建表时主键定义模板
            /// </summary>
            public const string CreateTablePKDef =
@"PRIMARY KEY (""{pk_column_name}"")
";

            /// <summary>
            /// 索引定义模板
            /// </summary>
            public const string IndexDef =
@"DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename = '{table_name}' AND indexname = '{index_name}') THEN
        CREATE INDEX ""{index_name}"" ON ""{table_name}"" ({index_column_colle}) INCLUDE ({index_column_colle});
    END IF; 
END $$;";

            /// <summary>
            /// 修改列模板
            /// </summary>
            public const string ColumnModify =
@"alter table ""{table_name}"" {column_modify_details} ;";

            /// <summary>
            /// 新增列模板
            /// </summary>
            public const string ColumnAdd =
@"DO $$ 
BEGIN 
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = '{table_name}' AND column_name = '{column_name}') THEN 
        ALTER TABLE ""{table_name}"" ADD ""{column_name}"" {data_type} {nullable} {default};
    END IF; 
END $$;";
            /// <summary>
            /// 删除列模板
            /// </summary>
            public const string ColumnDrop =
@"alter table ""{table_name}"" drop column ""{column_name}"";
";


            public const string INSERT =
@"insert into {table_name} ({column_names}) values ({column_values});
";

            public const string DELETE =
@"delete from {table_name} {condition};
";

            /// <summary>
            /// 添加某列的默认值约束
            /// </summary>
            public const string DefaultChange =
@"ALTER TABLE {table_name} alter column {column_names} set {default};";
            #endregion

            /// <summary>
            /// 后期改成可配置
            /// </summary>
            public readonly static HashSet<string> FunctionList = new HashSet<string>
            {
                "current_timestamp", "current_date"
            };

            /// <summary>
            /// 数值类型字典
            /// </summary>
            public readonly static HashSet<string> ValueType = new HashSet<string>
            {
                DatabaseColumnType.Bit.ToString().ToLower(),
                DatabaseColumnType.TinyInt.ToString().ToLower(),
                DatabaseColumnType.SmallInt.ToString().ToLower(),
                DatabaseColumnType.Int.ToString().ToLower(),
                DatabaseColumnType.BigInt.ToString().ToLower(),
                DatabaseColumnType.Decimal.ToString().ToLower(),
                DatabaseColumnType.Binary.ToString().ToLower(),
            };
        }
    }
}
